package com.graduate.daydayup.util;

import android.content.Context;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;

import com.blankj.utilcode.util.LogUtils;
import com.blankj.utilcode.util.ToastUtils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <pre>
 *     author: Bruce Yang
 *     blog  : https://yangjianan.gitee.io
 *     time  : 2019/11/7
 *     desc  : Excel 读写工具类
 *     link  : https://blog.csdn.net/u014743890/article/details/91045033
 * </pre>
 */
public class ExcelUtil {
    private static final String TAG = ExcelUtil.class.getSimpleName();

    //-------------------------动态读写Excel start-------------------------------//
    public static List<Map<Integer, Object>> readExcelNew(Context context, Uri uri, String filePath) {
        List<Map<Integer, Object>> list = null;
        Workbook wb;
        if (filePath == null) {
            return null;
        }
        String extString;
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            ToastUtils.showShort("格式有误");
            return null;
        }
        extString = filePath.substring(filePath.lastIndexOf("."));

        InputStream is;
        try {
            is = context.getContentResolver().openInputStream(uri);
            Log.i(TAG, "readExcel: " + extString);
            if (".xls".equals(extString)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
            if (wb != null) {
                // 用来存放表中数据
                list = new ArrayList<>();
                // 获取第一个sheet
                Sheet sheet = wb.getSheetAt(0);
                // 获取第一行标题
                Row rowHeader = sheet.getRow(0);
                int cellsCount = rowHeader.getPhysicalNumberOfCells();
                //存放标题map
                Map<Integer, Object> headerMap = new HashMap<>();
                for (int c = 0; c < cellsCount; c++) {
                    Object value = getCellFormatValue(rowHeader.getCell(c));
                    String cellInfo = "header " + "; c:" + c + "; v:" + value;
                    Log.i(TAG, "readExcelNew: " + cellInfo);

                    //判空，标题有空忽略
                    if (!TextUtils.isEmpty((String)value)) {
                        headerMap.put(c, value);
                    }
                }
                //添加标题到list
                list.add(headerMap);

                // 获取最大行数
                int rownum = sheet.getPhysicalNumberOfRows();
                // 获取最大列数
                int colnum = headerMap.size();
                for (int i = 1; i < rownum; i++) {
                    Row row = sheet.getRow(i);
                    //存放子内容
                    Map<Integer, Object> itemMap = new HashMap<>();
                    if (row != null) {
                        for (int j = 0; j < colnum; j++) {
                            Object value = getCellFormatValue(row.getCell(j));
                            String cellInfo = "r: " + i + "; c:" + j + "; v:" + value;
                            Log.i(TAG, "readExcelNew: " + cellInfo);

                            itemMap.put(j, value);
                        }
                    } else {
                        break;
                    }
                    list.add(itemMap);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            ToastUtils.showShort(" 导入失败");
            Log.e(TAG, "readExcelNew: 导入失败 " + e);
            LogUtils.file("导入失败，" + e);
        }
        return list;
    }

    public static void writeExcelNew(Context context, List<Map<Integer, Object>> exportExcel, Uri uri) {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("Sheet1"));

            int colums = exportExcel.get(0).size();
            for (int i = 0; i < colums; i++) {
                //设置默认宽度15字符。自适应无用，缺少java的类文件
                sheet.setColumnWidth(i, 15 * 256);
            }

            for (int i = 0; i < exportExcel.size(); i++) {
                Row row = sheet.createRow(i);
                Map<Integer, Object> integerObjectMap = exportExcel.get(i);
                for (int j = 0; j < colums; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(String.valueOf(integerObjectMap.get(j)));

                    //sheet.setColumnWidth(j, String.valueOf(integerObjectMap.get(j)).length() * 256);
                }
            }

            OutputStream outputStream = context.getContentResolver().openOutputStream(uri);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            ToastUtils.showShort("导出成功");
            Log.i(TAG, "writeExcel: 导出成功");
            LogUtils.file("writeExcel: 导出成功");
        } catch (Exception e) {
            e.printStackTrace();
            ToastUtils.showShort("导出失败");
            Log.e(TAG, "writeExcel: 导出失败" + e);
            LogUtils.file("导出失败，" + e);
        }
    }
    //-------------------------读取动态Excel end-------------------------------//

    /**
     * 获取单个单元格数据
     *
     * @param cell </>
     * @return cell
     */
    private static Object getCellFormatValue(Cell cell) {
        Object cellValue;
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC: {
                    // TODO: 2019/11/7
//                    cellValue = String.valueOf(cell.getNumericCellValue());
                    cellValue = new BigDecimal(cell.getNumericCellValue()).toPlainString();
                    break;
                }
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        // 数字
                        // TODO: 2019/11/7
//                        cellValue = String.valueOf(cell.getNumericCellValue());
                        cellValue = new BigDecimal(cell.getNumericCellValue()).toPlainString();
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
}
